package server;

import iface.HvZUser;
import iface.Logger;
import iface.Message;
import iface.Registration;
import iface.Status;
import iface.UserTypes;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * Handles connections and queries to the database.
 * 
 * @author Terry
 */
public class Database implements ClientQueryable {

    private static Database instance = null;
    private static final int MAX_USER_LENGTH = 32;
    private static final int MAX_PHONE_LENGTH = 12;
    private final String dbURL = "jdbc:mysql://localhost/";
    private String db = "account_db";
    private final String dbUser = "hvz_user";
    private final String dbPassword = "hvz_password";

    /*
     * Constructor for database.
     */
    protected Database() {
        // Prevent others from calling constructor.
        Logger.log("DATABASE: The database instance has been created.");
    }

    /**
     * @return an instance of the Database class.
     */
    public static Database getInstance() {
        if (instance == null)
            instance = new Database();

        return instance;
    }

    /**
     * @return A Status containing a collection of zombies
     */
    public Status getZombiesList() {
        return getUserList(UserTypes.ZOMBIE);
    }

    /**
     * @return A Status containing a collection of humans
     */
    public Status getHumansList() {
        return getUserList(UserTypes.HUMAN);
    }

    /**
     * @return A Status containing a collection of observers
     */
    public Status getObserversList() {
        return getUserList(UserTypes.OBSERVER);
    }

    /*
     * Returns a Status containing a list of users. The list returned is
     * determined by the given UserTypes enum.
     */
    private Status getUserList(UserTypes type) {
        try {

            // Determine what kind of users to search for.
            String statement = "SELECT * FROM users WHERE type = ?";
            int usr_type = 0;
            if (type.equals(UserTypes.ZOMBIE)) {
                usr_type = 1;
            } else if (type.equals(UserTypes.OBSERVER)) {
                usr_type = 2;
            }

            // Establish connection and sanitize the query
            Connection conn = establishConnection(dbUser, dbPassword);
            if (conn == null)
                return null;

            // Prepare the statement.
            PreparedStatement query = conn.prepareStatement(statement);
            query.setInt(1, usr_type);

            // Execute the query
            ResultSet rs = query.executeQuery();

            // Prepare the Status to return
            Status status = new Status(Status.Result.SUCCESS,
                    Message.MsgType.LOCATION);
            List<HvZUser> ll = new LinkedList<HvZUser>();
            status.setLocationList(ll);

            // If executing the SQL query returns nothing, return a Status
            // indicating so.
            if (!rs.first()) {
                conn.close();
                if (type.equals(UserTypes.ZOMBIE))
                    status.setMessage("No zombies found");
                else if (type.equals(UserTypes.HUMAN))
                    status.setMessage("No humans found");
                else
                    status.setMessage("No observers found");
                return status;
            }

            // Otherwise, build and populate the linkedList
            do {
                String net_id = rs.getString(1);
                String phone = rs.getString(2);
                UserTypes type_u = convertIntToType(rs.getInt(3));

                ll.add(new HvZUser(net_id, type_u, phone));
            } while (rs.next());

            conn.close();
            return status;
        } catch (SQLException e) {
            Logger.error("UNKNOWN|SQL: getUserList : " + e.toString());
            return null;
        }
    }

    /**
     * Register a new user and inserts the user into the database.
     * 
     * @param user
     *            a registration with a username, type status and phone number.
     *            Usernames must be less or equal to 32 characters long.
     * @return a Status indicating if the action was successful or not, null if
     *         there was a connection errors.
     */
    public Status registerNewUser(Registration user) {

        // Check that no null values are given
        if (user == null) {
            Logger.error("DB : Registration was null");
            Status s = new Status(Status.Result.ERROR,
                    Message.MsgType.REGISTRATION);
            s.setMessage("A registration error has occured");
            return s;
            // Check that username is correct length
        } else if (user.getUser().length() > MAX_USER_LENGTH) {
            Logger.verbose("DB: username too long: " + user.toString());
            Status s = new Status(Status.Result.ERROR,
                    Message.MsgType.REGISTRATION);
            s.setMessage("The username was too long. Please ensure the username less than "
                    + MAX_USER_LENGTH + " characters");
            return s;
        } else if (user.getPhoneNo().length() > 12) {
            Logger.verbose("DB: phone too long:" + user.toString());
            Status s = new Status(Status.Result.ERROR,
                    Message.MsgType.REGISTRATION);
            s.setMessage("The phone number was too long. Please ensure the phone number is less than "
                    + MAX_PHONE_LENGTH + "long.");
            return s;
        }

        try {
            String net_id = user.getUser();
            String phone = user.getPhoneNo();

            int type = 0;

            if (user.isZombie())
                type = 1;
            else if (user.getUserType().equals(UserTypes.OBSERVER))
                type = 2;

            String statement = "INSERT INTO users VALUES (?, ?, ?)";

            // Establish connection and sanitize the query
            Connection conn = establishConnection(dbUser, dbPassword);
            if (conn == null)
                return null;

            // Setup a multi-query transaction
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);

            // Check for duplicates
            ResultSet possibleDuplicates = findUser(phone, net_id, conn);
            if (possibleDuplicates.first()) {
                Status status = new Status(Status.Result.ERROR,
                        Message.MsgType.REGISTRATION);
                boolean dupName = possibleDuplicates.getString(1)
                        .equals(net_id);
                boolean dupPhone = possibleDuplicates.getString(2)
                        .equals(phone);

                if (dupName && dupPhone) {
                    status.setMessage("Unable to register user: "
                            + "The username and phone number are already registered.");
                } else if (dupName) {
                    status.setMessage("Unable to register user: "
                            + "The username is already registered");
                } else {
                    status.setMessage("Unable to register user: "
                            + "The phone number is already registered");
                }
                Logger.log("DB|SQL: User failed to register: "
                        + user.toString() + " : " + status.getMessage());
                conn.rollback();
                conn.setAutoCommit(true);
                conn.close();
                return status;
            }

            PreparedStatement ps = conn.prepareStatement(statement);
            ps.setString(1, net_id);
            ps.setString(2, phone);
            ps.setInt(3, type);

            ps.executeUpdate();

            Status status = new Status(Status.Result.SUCCESS,
                    Message.MsgType.REGISTRATION);
            status.setMessage("User " + net_id
                    + " has been successfully registered.");

            Logger.log("BD|SQL: User registered: " + user.toString());

            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
            return status;
        } catch (SQLException e) {
            Logger.error("UNKOWN|SQL: registerNewUser : " + e.toString()
                    + " : " + user.toString());
            return null;
        }
    }

    /**
     * Changes the given user into a zombie. If t he user is already a zombie,
     * then an error is returned. Observers cannot be changed into a zombie.
     * 
     * @param username
     *            the user to change
     * @return a Status indicating if the action was successful or not, null if
     *         there was a connection error.
     */
    public Status changeToZombie(String username) {

        // Prevent nulls from querying the database.
        if (username == null) {
            Logger.error("Database : changeToZombie : username was null");
            Status s = new Status(Status.Result.ERROR, Message.MsgType.KILL);
            s.setMessage("ERORR: Unable to change the user to a zombie");
            return s;
        }

        try {
            Connection conn = establishConnection(dbUser, dbPassword);

            if (conn == null)
                return null;

            // Setup a multi-query transaction
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            conn.setAutoCommit(false);

            // Execute the query
            ResultSet check = findUser(null, username, conn);

            if (!check.first()) {
                // The ResultSet was empty (user not in db), so rollback
                // changes.
                Status status = new Status(Status.Result.ERROR,
                        Message.MsgType.KILL);
                status.setMessage("The user " + username + " does not exist.");
                Logger.verbose("DB: Kill attempt on non-existant user: "
                        + username);
                conn.rollback();
                conn.setAutoCommit(true);
                conn.close();
                return status;
            } else if (check.getInt(3) > 0) {
                // Case that user is not human, so return an error
                // and rollback changes.

                Status status = new Status(Status.Result.ERROR,
                        Message.MsgType.KILL);
                if (check.getInt(3) == 1) {
                    status.setMessage("The user " + username
                            + " is already a zombie.");
                } else {
                    status.setMessage("Observers cannot be killed.");
                }
                Logger.verbose("DB: Failed kill: " + username + " : "
                        + status.getMessage());

                conn.rollback();
                conn.setAutoCommit(true);
                conn.close();
                return status;
            }

            // The user is a human, so kill them.
            String statement = "UPDATE users SET type = 1 WHERE net_id = ?";

            // Establish connection and sanitize the query
            PreparedStatement query = conn.prepareStatement(statement);
            query.setString(1, username);

            query.executeUpdate();

            Status status = new Status(Status.Result.SUCCESS,
                    Message.MsgType.KILL);
            status.setMessage(username + " has been turned into a zombie.");

            Logger.verbose("DB: User " + username + " has been killed");

            conn.commit();
            conn.setAutoCommit(true);
            conn.close();
            return status;
        } catch (SQLException e) {
            Logger.error("UNKOWN|SQL: changeToZombie : " + e.toString() + " : "
                    + username);
            return null;
        }
    }

    /**
     * Set which database the class connects to. Default: account_db
     * 
     * @param db
     *            the database this class should connect to.
     */
    public void setDatabase(String db) {
        Logger.verbose("DB: Database changed to " + db);
        this.db = db;
    }

    /**
     * @return the database name
     */
    public String getDatabase() {
        return db;
    }

    // Returns the results of searching the user.
    // You can either phone or name be null if need be.
    private ResultSet findUser(String phone, String name, Connection conn)
            throws SQLException {
        PreparedStatement ps = conn
                .prepareStatement("SELECT * FROM Users WHERE phone = ? OR net_id = ?");
        ps.setString(1, phone);
        ps.setString(2, name);
        return ps.executeQuery();
    }

    /*
     * Establishes a connection to the SQL database. It is the caller's
     * responsibility to close the connection when done.
     */
    protected Connection establishConnection(String user, String pw) {
        try {
            // Connect to the SQL database
            Class.forName("com.mysql.jdbc.Driver");
            Connection myCon = DriverManager
                    .getConnection(dbURL + db, user, pw);

            return myCon;
        } catch (ClassNotFoundException e) {
            Logger.error("SQL Connection: Driver error : " + e.toString());
            return null;
        } catch (SQLException e) {
            Logger.error("SQL Connection: " + db + " : " + e.toString());
            return null;
        }

    }

    /**
     * Checks if the user exists in the database
     * 
     * @return true if the user exists in the database, false otherwise
     * @param username
     *            the user to search for
     */
    public boolean doesUserExist(String username) {
        return null != getUser(username);
    }

    /**
     * Returns the user if they exists in the database
     * 
     * @param username
     *            the server to return
     * @return an HvZUser, null if the user is not in the database
     */
    public HvZUser getUser(String username) {
        try {
            Connection conn = establishConnection(dbUser, dbPassword);
            if (conn == null)
                return null;

            ResultSet result = findUser(null, username, conn);

            // Did we find anything?;
            if (!result.first())
                return null;
            assert (result.getFetchSize() == 1);

            String net_id = result.getString(1);
            String phone = result.getString(2);
            Logger.verbose("DB: getUser : " + username);
            UserTypes type = convertIntToType(result.getInt(3));
            return new HvZUser(net_id, type, phone);
        } catch (SQLException e) {
            Logger.error("SQL error, unable to process results from query: "
                    + e.toString());
            return null;
        }
    }

    /**
     * Converts ints with values 0 to 2 inclusive to UserTypes
     * 
     * @param val
     *            the int to convert. Range [0, 2]
     * @return a UserType. Can be Human, Zombie or Observer.
     * @throws IllegalArgumentException
     *             invalid int
     */
    protected UserTypes convertIntToType(int val) {
        switch (val) {
        case 0:
            return UserTypes.HUMAN;
        case 1:
            return UserTypes.ZOMBIE;
        case 2:
            return UserTypes.OBSERVER;
        default:
            throw new IllegalArgumentException();
        }
    }
}